package com.sunwayworld.baseline.framework.utils;

import com.sunwayworld.framework.context.ApplicationContextHelper;
import com.sunwayworld.framework.io.file.FileScope;
import com.sunwayworld.framework.utils.EncryptUtils;
import com.sunwayworld.framework.utils.FileUtils;
import com.sunwayworld.framework.utils.StringUtils;
import com.sunwayworld.module.item.file.bean.CoreFileBean;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.lang.reflect.Method;
import java.time.LocalDateTime;

public class ExcelUtils {
    public static String getField(Object o, String args) {
        Class<? extends Object> cls = o.getClass();
        Method method = null;
        Object invoke = null;

        try {
            if (StringUtils.startsWith(args, "getExt$Item")) {              //解析动态参数:  String field = ExcelUtils.getField(sampleBatchBean, getExt$Item(\"customer\")");
                method = cls.getMethod("getExt$Item", String.class);
                invoke = method.invoke(o,args.split("\"")[1]);
            } else {
                //判断字段是否存在
                // Field field = cls.getDeclaredField(args);
                // if (field == null) {
                //     field = cls.getField(args);
                // }
                // if (field == null) {
                //     return "";
                // }
                String fieldName = "get" + args.substring(0, 1).toUpperCase() + (args.length() > 1 ? args.substring(1) : "");
                method = cls.getMethod(fieldName);
                invoke = method.invoke(o);
            }
            return invoke == null?"":invoke.toString();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return "";
    }

    /**
     * 合并单元格
     */
    public static void cellRangeAddress(Sheet sheet, int a, int b, int c, int d) {
        CellRangeAddress rangeMergedRegion = new CellRangeAddress(a, b, c, d);
        sheet.addMergedRegion(rangeMergedRegion);
    }

    public static Sheet createSheet(Workbook wb, String sheetName) {
        Sheet sheet = wb.createSheet(sheetName);
        // 隐藏Sheet的网络线
        //sheet.setDisplayGridlines(false);
        // 缩放比例
        sheet.setZoom(85);
        return sheet;
    }

    public static CellStyle getRowCellStyleNoBorder(Workbook wb) {
        DataFormat format = wb.createDataFormat();
        CellStyle style = wb.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);// 水平
        style.setVerticalAlignment(VerticalAlignment.CENTER);// 垂直
        style.setFont(getFont(wb, true));
        style.setDataFormat(format.getFormat("@"));// 设置单元格格式为：文本格式
        return style;
    }

    public static CellStyle getCaptionCellStyle(Workbook wb) {
        DataFormat format = wb.createDataFormat();
        CellStyle style = wb.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);// 水平
        style.setVerticalAlignment(VerticalAlignment.CENTER);// 垂直
        style.setFont(getFont(wb, true));
        style.setBorderBottom(BorderStyle.THIN);
        style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderLeft(BorderStyle.THIN);
        style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderRight(BorderStyle.THIN);
        style.setRightBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderTop(BorderStyle.THIN);
        style.setTopBorderColor(IndexedColors.BLACK.getIndex());
        style.setDataFormat(format.getFormat("@"));// 设置单元格格式为：文本格式
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        style.setFillForegroundColor(IndexedColors.PALE_BLUE.getIndex());
        return style;
    }

    public static CellStyle getRowCellStyle(Workbook wb, String _format) {
        DataFormat format = wb.createDataFormat();
        CellStyle style = wb.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);// 水平
        style.setVerticalAlignment(VerticalAlignment.CENTER);// 垂直
        style.setFont(getFont(wb, false));//设置字体
        style.setBorderBottom(BorderStyle.THIN);
        style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderLeft(BorderStyle.THIN);
        style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderRight(BorderStyle.THIN);
        style.setRightBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderTop(BorderStyle.THIN);
        style.setTopBorderColor(IndexedColors.BLACK.getIndex());
        style.setDataFormat(format.getFormat(_format));// 设置单元格格式为：文本格式
        return style;
    }

    public static Font getFont(Workbook wb, boolean bold) {
        Font font = wb.createFont();
        font.setFontHeightInPoints((short) 11);
        font.setFontName("微软雅黑");
        font.setBold(bold);
        return font;
    }

    public static CoreFileBean getCoreFile(long fileSize, String preName, Long targetId, String targetTableName, String postfix) {
        Long id = ApplicationContextHelper.getNextIdentity();
        CoreFileBean insertCoreFile = new CoreFileBean();
        insertCoreFile.setId(id);
        insertCoreFile.setTargetId(targetTableName + "$" + targetId);
        insertCoreFile.setName(preName + "." + postfix);
        insertCoreFile.setFileExt(FileUtils.getFileExtension(insertCoreFile.getName()));
        insertCoreFile.setMd5Name(EncryptUtils.MD5Encrypt(id + "$" + insertCoreFile.getName()));
        insertCoreFile.setScope(FileScope.secure.name());
        insertCoreFile.setSize(fileSize);
        insertCoreFile.setTimes(0L);
        insertCoreFile.setExt(postfix);
        insertCoreFile.setDownloadable("1");
        insertCoreFile.setCreatedTime(LocalDateTime.now());

        return insertCoreFile;
    }

    /**
     * 合并单元格创建表头
     * @param wb 工作簿
     * @param sheet sheet页
     * @param rowIndex 要创建合并行的行号
     * @param a 单元格开始行号
     * @param b 单元格结束行号
     * @param c 单元格开始列号
     * @param d 单元格结束列号
     * @param textTitle 要设置的标题文本
     */
    public static void createSheetTitleRow(XSSFWorkbook wb, Sheet sheet, int rowIndex, int a, int b, int c, int d, String textTitle){
        CellRangeAddress rangeMergedRegion = new CellRangeAddress(a, b, c, d);
        sheet.addMergedRegion(rangeMergedRegion);
        Row r1 = sheet.createRow(rowIndex);
        r1.setHeightInPoints(30);                                   //设置行高
        Cell c1 = r1.createCell(0);
        c1.setCellValue(textTitle);
        XSSFCellStyle s1 = wb.createCellStyle();                    //创建样式
        //设置字体样式
        Font font = wb.createFont();
        font.setFontHeightInPoints((short) 14);
        font.setFontName("微软雅黑");
        font.setBold(true);
        s1.setFont(font);
        s1.setAlignment(HorizontalAlignment.CENTER);                //设置水平对齐方式
        s1.setVerticalAlignment(VerticalAlignment.CENTER);          //设置垂直对齐方式
        //s1.setFillPattern(FillPatternType.SOLID_FOREGROUND);        //设置填充颜色
        //s1.setFillForegroundColor(IndexedColors.PINK.getIndex()); //设置背景颜色,参考https://blog.csdn.net/weixin_41722928/article/details/86305378
        c1.setCellStyle(s1);
    }


    /**
     * 创建单元格,形如: 姓名| zhangsan  ,这样的同一行的两个个单元格
     * @param wb 工作簿
     * @param sheet sheet页
     * @param rowIndex 行号
     * @param cell1Start 第一个合并的单元格开始列号
     * @param cell1End   第一个合并的单元格结束列号
     * @param cell2Start 第二个合并的单元格开始列号
     * @param cell2End   第二个合并的单元格结束列号
     * @param cell1Text  第一个合并的单元格值
     * @param cell2Text  第二个合并的单元格值
     */
    public static void createDoubleCell(XSSFWorkbook wb, Sheet sheet, int rowIndex, int cell1Start, int cell1End, int cell2Start, int cell2End, String cell1Text,String cell2Text){
        //合并单元格
        CellRangeAddress mergeCell1 = new CellRangeAddress(rowIndex, rowIndex, cell1Start, cell1End);
        sheet.addMergedRegion(mergeCell1);
        CellRangeAddress mergeCell2 = new CellRangeAddress(rowIndex, rowIndex, cell2Start, cell2End);
        sheet.addMergedRegion(mergeCell2);
        //设置样式
        XSSFCellStyle style = wb.createCellStyle();                    //创建样式
        Font font = wb.createFont();                                   //设置字体样式
        font.setFontHeightInPoints((short) 12);
        font.setFontName("微软雅黑");
        font.setBold(false);
        style.setFont(font);
        style.setAlignment(HorizontalAlignment.CENTER);                //设置水平对齐方式
        style.setVerticalAlignment(VerticalAlignment.CENTER);          //设置垂直对齐方式
        style.setBorderBottom(BorderStyle.THIN);
        style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderLeft(BorderStyle.THIN);
        style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderRight(BorderStyle.THIN);
        style.setRightBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderTop(BorderStyle.THIN);
        style.setTopBorderColor(IndexedColors.BLACK.getIndex());
        style.setDataFormat(wb.createDataFormat().getFormat("@"));      // 设置单元格格式为：文本格式
        //设置同一行
        Row row = sheet.createRow(rowIndex);
        row.setHeightInPoints(30);   //设置行高
        Cell cell = null;
        for(int i=0;i<=cell2End;i++){
            cell = row.createCell(i);
            if(i == cell1Start){
                cell.setCellValue(cell1Text);
            }
            if(i == cell2Start){
                style.setAlignment(HorizontalAlignment.LEFT);                 //设置水平对齐方式
                cell.setCellValue(cell2Text);
            }
            cell.setCellStyle(style);
        }
    }

}
